<?php
/**
 * FaZend Framework
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt. It is also available 
 * through the world-wide-web at this URL: http://www.fazend.com/license
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@fazend.com so we can send you a copy immediately.
 *
 * @copyright Copyright (c) FaZend.com
 * @version $Id: Deployer.php 1587 2010-02-07 07:49:26Z yegor256@gmail.com $
 * @category FaZend
 */

/**
 * Deploy Db schema to the server (MySQL supported for now only)
 *
 * You may add your own pathes with .SQL files by means of app.ini in your project.
 * See application.ini for resources.Deployer.folders parameter.
 *
 * @package Deployer
 * @todo Refactor in order to support other DB servers
 */
class FaZend_Deployer
{

    const EXCEPTION_CLASS = 'FaZend_Deployer_Exception';

    /**
     * Instance of the class, singleton pattern
     *
     * @var FaZend_Deployer
     */
    protected static $_instance;

    /**
     * Configuration options
     *
     * @var Zend_Config
     */
    protected $_options;

    /**
     * Instance of DB deployer
     *
     * @param Zend_Config Configuration parameters
     * @return void
     * @throws FaZend_Deployer_InvalidConfig
     */
    public static function getInstance(Zend_Config $config = null) 
    {
        if (!isset(self::$_instance)) {
            if (is_null($config)) {
                FaZend_Exception::raise(
                    'FaZend_Deployer_InvalidConfig', 
                    "First time getInstance() should be called with valid config",
                    self::EXCEPTION_CLASS
                );
            }

            self::$_instance = new FaZend_Deployer($config);
        }

        return self::$_instance;
    }

    /**
     * Constructor
     *
     * @param Zend_Config Configuration parameters
     * @return void
     */
    protected function __construct(Zend_Config $options) 
    {
        $this->_options = $options;
    }

    /**
     * Deploy Db schema
     *
     * @return void
     * @throws FaZend_Deployer_Exception
     */
    public function deploy() 
    {
        // if it's turned off
        if (!$this->_options->deploy)
            return;
        
        // check the existence of the flag
        // it it's absent, we should do anything
        $flagFile = $this->_flagName();
        if (!file_exists($flagFile) && (APPLICATION_ENV === 'production'))
            return;

        // remove it
        // we will never come back here again
        if ((@unlink($flagFile) === false) && (APPLICATION_ENV === 'production')) {
            FaZend_Log::err('Failed to remove flag file: ' . $flagFile);
            return;
        }

        // if we can't get a list of tables in DB - we stop
        if (!method_exists($this->_db(), 'listTables'))
            return;
    
        // go through ALL deployment directories
        foreach ($this->_dirNames() as $dir) {
            if (!file_exists($dir) || !is_dir($dir))
                continue;

            try {
                // get full list of existing(!) tables in Db
                $tables = array_map(
                    create_function('$a', 'return strtolower($a);'), 
                    $this->_db()->listTables()
                );
            
                // get full list of files
                $files = preg_grep('/^\d+\s.*?\.sql$/', scandir($dir));

                // sort in right order
                usort($files, array($this, '_sorter'));

                // go through all .SQL files
                foreach ($files as $file) {
                    $matches = array();
                    preg_match('/^\d+\s(.*)\.sql$/', $file, $matches);
                    $table = $matches[1];

                    // this table already exists?
                    if (in_array(strtolower($table), $tables)) {
                        $this->_update($table, $this->_clearSql($dir . '/' . $file));
                    } else {
                        $this->_create($table, $this->_clearSql($dir . '/' . $file));
                    }
                }
            } catch (FaZend_Deployer_Exception $exception) {
                // if there is no email - show the error
                if (FaZend_Properties::get()->errors->email) {
                    // send email to the site admin admin
                    FaZend_Email::create('fazendDeployerException.tmpl')
                        ->set('toEmail', FaZend_Properties::get()->errors->email)
                        ->set('toName', 'admin')
                        ->set(
                            'subject', 
                            parse_url(WEBSITE_URL, PHP_URL_HOST) . 
                            ' database deployment exception, rev.' . FaZend_Revision::get()
                        )
                        ->set('text', $exception->getMessage())
                        ->send()
                        ->logError();
                }

                // throw it to the application
                throw $exception;
            } 
        }
    }

    /**
     * Get list of tables ready for deployment
     *
     * @return string[]
     */
    public function getTables() 
    {
        $list = array();

        foreach ($this->_dirNames() as $dir) {
            if (!file_exists($dir) || !is_dir($dir))
                continue;
            
            foreach (scandir($dir) as $file) {
                if (!preg_match('/^\d+\s(.*?)\.sql$/', $file, $matches))
                    continue;

                try {
                    $this->getTableInfo($matches[1]);
                } catch (FaZend_Deployer_NotTableButView $e) {
                    continue;
                }

                $list[] = $matches[1];
            }
        }
        
        return $list;
    }

    /**
     * Get table information
     *
     * @param string Name of the table
     * @return array[]
     * @throws FaZend_Deployer_SqlFileNotFound
     */
    public function getTableInfo($table) 
    {
        foreach ($this->_dirNames() as $dir) {
            if (!file_exists($dir) || !is_dir($dir))
                continue;
            foreach (scandir($dir) as $file)
                if (preg_match('/^\d+\s' . preg_quote($table) . '\.sql$/i', $file))
                    return $this->_sqlInfo(file_get_contents($dir . '/' . $file));
        }

        FaZend_Exception::raise(
            'FaZend_Deployer_SqlFileNotFound', 
            "File '<num> {$table}.sql' not found in '{$dir}'", self::EXCEPTION_CLASS
        );
    }

    /**
     * Get table information from sql
     *
     * @param string SQL
     * @return array[]
     */
    public function getSqlInfo($sql) 
    {
        return $this->_sqlInfo($sql);
    }

    /**
     * Location of .SQL files, returns list of directories
     *
     * @return array List of directories
     */
    protected function _dirNames() 
    {
        return $this->_options->folders;
    }

    /**
     * Location of the flag
     *
     * @return string
     */
    protected function _flagName()
    {
        return $this->_options->flag;
    }

    /**
     * Create new table
     *
     * @param string Name of the table
     * @param string SQL file content
     * @return void
     * @throws FaZend_Deployer_Exception
     */
    protected function _create($table, $sql) 
    {
        if (empty($sql)) {
            logg("DB table '{$table}' was NOT created since SQL is empty");
            return;
        }
        
        try {
            $this->_db()->query($sql);
        } catch (Exception $e) {
            FaZend_Exception::raise(
                'FaZend_Deployer_CreateFailed', 
                $e->getMessage() . ': ' . $sql, 
                self::EXCEPTION_CLASS
            );
        }
        
        // log the operation
        if ($this->_options->verbose)
            logg("DB table '{$table}' was created: {$sql}");
    }

    /**
     * Update existing table
     *
     * @param string Name of the table
     * @param string SQL file content
     * @return void
     */
    protected function _update($table, $sql) 
    {
        try {
            $infoSql = $this->_sqlInfo($sql);
        } catch (FaZend_Deployer_NotTableButView $e) {
            // this is VIEW, not table
            // we just drop and create again
            //$this->_db()->query("DROP VIEW $table");

            // create this VIEW again
            //$this->_create($table, $sql);
            return;
        }

        $infoDb = $this->_db()->describeTable($table);

        // tbd
        foreach ($infoSql as $column);

        // log the operation
        // FaZend_Log::info("DB table '{$table}' was updated: {$sql}");
    }

    /**
     * Get list of columns from SQL spec
     *
     * @param string SQL spec of the table
     * @return array[]
     */
    protected function _sqlInfo($sql) 
    {
        $sql = preg_replace(
            array(
                '/\-\-.*?\n/', // kill comments
                '/[\n\t\r]/', // no special chars
                '/\s+/', // compress spaces
                '/`/', // remove backticks
            ), 
            ' ', 
            $sql . "\n"
        );

        // no double spaces
        $sql = trim($sql);

        // sanity check
        if (!preg_match('/^create (?:table|view)?/i', $sql))
            FaZend_Exception::raise(
                'FaZend_Deployer_WrongFormat', 
                "Every SQL file should start with 'create table' or 'create view', ".
                "we get this: '" . cutLongLine($sql, 50) . "'",
                self::EXCEPTION_CLASS
            );

        // this is view, we just drop it and create new
        if (preg_match('/^create\s(?:or\sreplace\s)?view/i', $sql))
            FaZend_Exception::raise('FaZend_Deployer_NotTableButView');

        // cut out the text between starting and ending brackets
        $columnsText = substr($sql, strpos($sql, '(')+1);
        $columnsText = trim(substr($columnsText, 0, strrpos($columnsText, ')'))) . ', ';

        $matches = array();
        preg_match_all(
            '/([\w\d\_]+)\s+((?:[\w\_\s\d]|(?:\(.*?\)))+)(?:\scomment\s[\"\'](.*?)[\'\"])?\,/i', 
            $columnsText, 
            $matches
        );

        $info = array();
        foreach ($matches[0] as $id=>$column) {

            $key = array();

            // primary key
            if (preg_match('/^primary\skey\s?.*?\(\s?([^\,]*?)\s?\)/i', $column, $key)) {
                $info[$key[1]]['PRIMARY'] = 1;
                $info[$key[1]]['PRIMARY_POSITION'] = 1;
                $info[$key[1]]['IDENTITY'] = 1;
                continue;
            }

            // process foreign keys
            if (preg_match(
                '/^(?:constraint\s.*?\s)?foreign\skey\s\(\s?(.*?)\s?\)\sreferences\s(.*?)\s\(\s?(.*?)\s?\)\s(.*)/i', 
                $column, 
                $key
            )) {
                $info[$key[1]]['FK'] = 1;
                $info[$key[1]]['FK_TABLE'] = trim($key[2]);
                $info[$key[1]]['FK_COLUMN'] = trim($key[3]);

                if (strpos(strtolower($key[4]), 'on delete cascade') !== false)
                    $info[$key[1]]['FK_COMPOSITION'] = true;
                    
                continue;
            }

            // unique's
            if (preg_match('/^(?:unique|unique key)\s?\(\s?([^\,]*?)\s?\)/i', $column, $key)) {
                $info[$key[1]]['UNIQUE'] = 1;
                continue;
            }

            // other special mnemos, if not parsed above - skip them
            if (preg_match('/^(index|key|primary\skey|constraint|foreign\skey|unique|unique\skey)\s?\(/i', $column))
                continue;

            $info[$matches[1][$id]] = array(
                'COLUMN_NAME' => $matches[1][$id],
                'DATA_TYPE' => $matches[2][$id],
                'COMMENT' => $matches[3][$id],
                'NULL' => !preg_match('/not\snull/i', $matches[2][$id]),
            );
                
        }

        return $info;
    }

    /**
     * File name sorter
     *
     * @param string File name 1
     * @param string File name 2
     * @return int
     */
    protected function _sorter($file1, $file2) 
    {
        return (int)$file1 > (int)$file2;
    }

    /**
     * Db adapter
     *
     * @return Zend_Db_Adapter
     */
    protected function _db() 
    {
        return Zend_Db_Table::getDefaultAdapter();
    }

    /**
     * Clears SQL out of comments
     *
     * @param string Name of the SQL file
     * @return string
     */
    protected function _clearSql($file) 
    {
        return trim(
            preg_replace(
                array(
                    '/\-\-.*/',
                    '/[\n\r\t]/'
                ), 
                ' ', // replace with spaces
                "\n" . file_get_contents($file)
            )
        );
    }

}
